CREATE trigger tt_billlay_bd
on dbo.tt_billlay
instead of delete
as  
begin
  set nocount on
  declare @layid integer, @quantity decimal(18,4),
          @depotid integer, @productid integer,@costtype integer,@old_pid integer,
          @assqty decimal(18,4)
  declare cr1 cursor local forward_only static optimistic for 
    select layid, bquantity, depotid, productid, id, assqty from deleted
  open cr1
  fetch next from cr1 into @layid, @quantity, @depotid, @productid, @old_pid, @assqty
  while @@fetch_status = 0
  begin
    update tt_booklay 
    set beginqty = beginqty - @quantity, 
      beginassqty = beginassqty - @assqty,
      quantity = quantity - @quantity,
      assqty = assqty - @assqty 
    where productid = @productid and depotid = @depotid and layid = @layid
    delete from tt_billlay where id = @old_pid
    fetch next from cr1 into @layid, @quantity,@depotid,@productid,@old_pid,@assqty
  end
  close cr1
  deallocate cr1
  set nocount off
end
